<!---
	Wordpress to MachBlog 1.0 Conversion Script
	Daniel Schmid (www.danielschmid.name, daniel.schmid@gmail.com)
	History:
	v0.2 4/20/2007

	COMMENTS:
	* This script is for MySQL only.
	* This script assumes that you have configured a datasource for Wordpress and a datasource for MachBlog
	  in the ColdFusion or BlueDragon administrator. The databases need not be on the same server, they just
	  both have to be accessible in the code via a DSN.
	* This script must be run as a database user with create table rights in order to work, because it creates new
	  tables to manage people coming to the blog using old Wordpress URLs. This functionality also necessitates adding
	  a block of code to your Application.cfm (see below). If you don't want to or can't use a database owner user when
	  running this script, you may manually create two new tables prior to running this script as follows:

	  create table old_cat_to_new_cat(old_category_id int unsigned not null, new_category_id char(35) not null, primary key(old_category_id, new_category_id));
	  create table old_entry_to_new_entry(old_entry_id int unsigned not null, new_entry_id char(35) not null, primary key(old_entry_id, new_entry_id));

	* Wordpress stores the metadat of posts (like attached files in a post_metadata table). I ignored that at the moment

	EXECUTING THIS SCRIPT:
	* Set the DSN variables to the correct values for your installation prior to running the script.
	* Set the adminID variable to the value of the user ID you wish to be shown as the creator of all the records prior to running the script.
	  Note that for items such as comments this does not apply (meaning your admin user will not be associated as the user who made the comment).
	* If you wish to support the redirect of old permalinks from Wordpress to the new MachBlog pages, please put this the block of code
	  at the bottom of Application.cfm.
	  [start]
	 	<cfset machBlogDSN = "machblog" />

		<cfif structKeyExists(url, "p")>
			<cfquery name="getNewEntryID" datasource="#machBlogDSN#">
				select 	new_entry_id
				from 	old_entry_to_new_entry
				where 	old_entry_id = <cfqueryparam value="#url.p#" cfsqltype="cf_sql_integer" />
			</cfquery>

			<cfif getNewEntryID.recordCount eq 1>
				<cflocation url="index.cfm?event=showEntry&entryID=#getNewEntryID.new_entry_id#" addtoken="no" />
			<cfelse>
				<cfset message = urlEncodedFormat("The W entry ID you are attempting to view does not exist.") />
				<cflocation url="index.cfm?event=showHome_redirect&message=#message#" />
			</cfif>
		</cfif>

		<cfif structKeyExists(url, "cat")>
			<cfquery name="getNewCategoryID" datasource="#machBlogDSN#">
				select 	new_category_id
				from 	old_cat_to_new_cat
				where 	old_category_id = <cfqueryparam value="#url.cat#" cfsqltype="cf_sql_integer" />
			</cfquery>

			<cfif getNewCategoryID.recordCount eq 1>
				<cflocation url="index.cfm?event=showEntriesByCategory&categoryId=#getNewCategoryID.new_category_id#" addtoken="no" />
			<cfelse>
				<cfset message = urlEncodedFormat("The Wordpress category ID you are attempting to view does not exist.") />
				<cflocation url="index.cfm?event=showHome_redirect&message=#message#" />
			</cfif>
		</cfif>

	  [end]

	* only blog entries are mirgratedt NO STATIC PAGES FROM WORDPRESS ARE MIRGRATED
	* You will likely want to run the script with a requesttimeout in the URL since it takes a bit of time for this to run. Just throw a big
	  number in there like 10000 and you should be OK.
--->

<!--- set the respective datasources --->
<cfset WordpressDSN = "Wordpress" />
<cfset machblogDSN = "machblog" />

<!--- need an instance of the i18n CFC to handle the date to epoch conversions --->
<cfset i18nUtils = createObject("component", "machblog.org.hastings.locale.utils").init() />

<!--- SET THIS TO THE USER ID YOU WANT ASSOCIATED WITH ALL DATABASE ACTIVITY --->
<cfset adminID = "F2D2EE50-1111-1927-82EDC2833CAC6792" />

<!--- start the fun ... --->
<h3>Wordpress to MachBlog Conversion</h3>

Creating entry and category mapping tables ...<br />
<cfflush />

<cfquery name="createEntryMappingTable" datasource="#machblogDSN#">
	create table old_entry_to_new_entry (
					old_entry_id int unsigned not null,
					new_entry_id char(35) not null,
					primary key(old_entry_id, new_entry_id)
				 );

</cfquery>

<cfquery name="createCategoryMappingTable" datasource="#machblogDSN#">
	create table old_cat_to_new_cat (
					old_category_id int unsigned not null,
					new_category_id char(35) not null,
					primary key(old_category_id, new_category_id)
				  );
</cfquery>

Successfully created mapping tables.<br />
<br />

Getting categories from Wordpress ...<br />
<cfflush />

<cfquery name="WordpressCategories" datasource="#WordpressDSN#">
	select 	cat_name,
			cat_id
	from 	wp_categories
	order by cat_name asc
</cfquery>

Retrieved <cfoutput>#WordpressCategories.recordCount#</cfoutput> categories from Wordpress.<br />
<br />

Inserting Wordpress categories into MachBlog database and populating category mapping table ...<br />
<cfflush />

<cfloop query="WordpressCategories">
	<cfset categoryID = createUUID() />

	<cftransaction action="begin">
		<cfquery name="insertCategory" datasource="#machblogDSN#">
			insert into machblog_category (
				category_id,
				category_name,
				created_by_id,
				dt_created,
				ip_created,
				is_active
			) values (
				<cfqueryparam value="#categoryID#" cfsqltype="cf_sql_char" />,
				<cfqueryparam value="#trim(WordpressCategories.cat_name)#" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="#adminID#" cfsqltype="cf_sql_char" />,
				<cfqueryparam value="#i18nUtils.toEpoch(now())#" cfsqltype="cf_sql_bigint" />,
				<cfqueryparam value="127.0.0.1" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="1" cfsqltype="cf_sql_tinyint" />
			)
		</cfquery>

		<cfquery name="insertCategoryMap" datasource="#machblogDSN#">
			insert into old_cat_to_new_cat (
				old_category_id,
				new_category_id
			) values (
				<cfqueryparam value="#WordpressCategories.cat_id#" cfsqltype="cf_sql_integer" />,
				<cfqueryparam value="#categoryID#" cfsqltype="cf_sql_char" />
			)
		</cfquery>
	</cftransaction>
</cfloop>

Wordpress categories migrated.<br />
<br />

Getting entries and entry categories from Wordpress ...<br />
<cfflush />

<cfquery name="WordpressEntries" datasource="#WordpressDSN#">
	select 	*
	from 	wp_posts
	where post_status ='publish'
	order by post_date asc
</cfquery>

<cfquery name="WordpressEntryCats" datasource="#WordpressDSN#">
	select 	category_id,
			post_id
	from 	wp_post2cat
</cfquery>

Retrieved <cfoutput>#WordpressEntries.recordCount#</cfoutput> entries from Wordpress.<br />
<br />

Inserting entries and entry categories into MachBlog database and populating entry mapping table ...<br />
<cfflush />

<cfloop query="WordpressEntries">
	<cfset newEntryID = createUUID() />

	<cftransaction action="begin">
		<cfquery name="insertEntry" datasource="#machblogDSN#">
			insert into machblog_entry (
				entry_id,
				title,
				body,
				allow_comments,
				allow_trackbacks,
				dt_posted,
				created_by_id,
				dt_created,
				ip_created,
				is_active
			) values (
				<cfqueryparam value="#newEntryID#" cfsqltype="cf_sql_char" />,
				<cfqueryparam value="#WordpressEntries.post_title#" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="#WordpressEntries.post_content#" cfsqltype="cf_sql_longvarchar" />,
				<cfqueryparam value="#IIf(WordpressEntries.comment_status eq 'open', DE(1), DE(0))#" cfsqltype="cf_sql_tinyint" />,
				<cfqueryparam value="#IIf(WordpressEntries.ping_status eq 'open', DE(1), DE(0))#" cfsqltype="cf_sql_tinyint" />,
				<cfqueryparam value="#i18nUtils.toEpoch(WordpressEntries.post_date)#" cfsqltype="cf_sql_bigint" />,
				<cfqueryparam value="#adminID#" cfsqltype="cf_sql_char" />,
				<cfqueryparam value="#i18nUtils.toEpoch(now())#" cfsqltype="cf_sql_bigint" />,
				<cfqueryparam value="127.0.0.1" cfsqltype="cf_sql_varchar" />,
				<cfqueryparam value="#IIf(WordpressEntries.post_status eq 'publish', DE(1), DE(0))#" cfsqltype="cf_sql_tinyint" />
			)
		</cfquery>

		<cfquery name="insertEntryMapping" datasource="#machblogDSN#">
			insert into old_entry_to_new_entry (
				old_entry_id,
				new_entry_id
			) values (
				<cfqueryparam value="#WordpressEntries.id#" cfsqltype="cf_sql_integer" />,
				<cfqueryparam value="#newEntryID#" cfsqltype="cf_sql_char" />
			)
		</cfquery>

		<!--- get the category IDs from Wordpress and convert to MachBlog categories --->
		<cfquery name="getCategoryIDs" dbtype="query">
			select category_ID as oldCategoryID
			from WordpressEntryCats
			where post_id = <cfqueryparam value="#WordpressEntries.ID#" cfsqltype="cf_sql_integer" />
		</cfquery>

		<cfloop query="getCategoryIDs">
			<!--- get the new category ID --->
			<cfquery name="getNewCategoryID" datasource="#machblogDSN#">
				select new_category_id as newCategoryID
				from old_cat_to_new_cat
				where old_category_id = <cfqueryparam value="#getCategoryIDs.oldCategoryID#">
			</cfquery>

			<cfquery name="insertEntryCategory" datasource="#machblogDSN#">
				insert into machblog_entry_category (
					entry_id,
					category_id
				) values (
					<cfqueryparam value="#newEntryID#" cfsqltype="cf_sql_char" />,
					<cfqueryparam value="#getNewCategoryID.newCategoryID#" cfsqltype="cf_sql_char" />
				)
			</cfquery>
		</cfloop>
	</cftransaction>
</cfloop>

Entries and categories inserted.<br />
<br />
Retrieving comments from Wordpress ...<br />
<cfflush />

<cfquery name="WordpressComments" datasource="#WordpressDSN#">
	select 	comment_ID,
			comment_post_ID,
			comment_content,
			comment_author_email,
			comment_author,
			comment_author_url,
			comment_date,
			comment_author_IP,
			comment_approved
	from 	wp_comments
	where comment_approved != 'spam'
	order by comment_date
</cfquery>

Retrieved <cfoutput>#WordpressComments.recordCount#</cfoutput> comments from Wordpress.<br />
<br />

Inserting comments into MachBlog ...<br />
<cfflush />

<cfloop query="WordpressComments">
	<cfset commentID = createUUID() />

	<!--- get the new entry id from the old one --->
	<cfquery name="getNewEntryID" datasource="#machblogDSN#">
		select 	new_entry_id as newEntryID
		from 	old_entry_to_new_entry
		where 	old_entry_id = <cfqueryparam value="#WordpressComments.comment_post_ID#" cfsqltype="cf_sql_integer" />
	</cfquery>

	<cfquery name="insertComment" datasource="#machblogDSN#">
		insert into machblog_comment (
			comment_id,
			entry_id,
			name,
			email,
			url,
			comment,
			is_subscribed,
			subscription_only,
			subscriber_email_sent,
			dt_created,
			ip_created,
			is_active
		) values (
			<cfqueryparam value="#commentID#" cfsqltype="cf_sql_char" />,
			<cfqueryparam value="#getNewEntryID.newEntryID#" cfsqltype="cf_sql_char" />,
			<cfqueryparam value="#trim(WordpressComments.comment_author)#" cfsqltype="cf_sql_varchar" />,
			<cfqueryparam value="#trim(WordpressComments.comment_author_email)#" cfsqltype="cf_sql_varchar" null="#not len(WordpressComments.comment_author_email)#" />,
			<cfqueryparam value="#trim(WordpressComments.comment_author_url)#" cfsqltype="cf_sql_varchar" null="#not len(WordpressComments.comment_author_url)#" />,
			<cfqueryparam value="#trim(WordpressComments.comment_content)#" cfsqltype="cf_sql_longvarchar" />,
			<cfqueryparam value="0" cfsqltype="cf_sql_tinyint" />,
			<cfqueryparam value="0" cfsqltype="cf_sql_tinyint" />,
			<cfqueryparam value="1" cfsqltype="cf_sql_tinyint" />,
			<cfqueryparam value="#i18nUtils.toEpoch(now())#" cfsqltype="cf_sql_bigint" />,
			<cfqueryparam value="#trim(WordpressComments.comment_author_ip)#" cfsqltype="cf_sql_varchar" />,
			<cfqueryparam value="#val(WordpressComments.comment_approved)#" cfsqltype="cf_sql_tinyint" />
		)
	</cfquery>
</cfloop>

Completed inserting comments into MachBlog.<br />
<br />


<strong>Migration complete.</strong>
